package org.finalministry.database.dao;

import geodb.GeoDB;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.finalministry.database.connection.IDatabaseConnection;
import org.finalministry.objects.osm.OSM_Bounds;
import org.finalministry.objects.osm.OSM_Node;
import org.finalministry.objects.osm.OSM_Tag;
import org.finalministry.objects.osm.OSM_Way;
import org.finalministry.objects.osm.OSM_Way_Node_Ref;
import org.finalministry.utils.geo.GeoUtils;

/**
 * <h3>Tables:</h3>
 * <ul>
 * <li>OSM_ORIGIN</li>
 * </ul>
 * See also: <a href="http://wiki.openstreetmap.org/wiki/.osm"
 * target="n">openstreetmap wiki on osm</a> and <a
 * href="http://wiki.openstreetmap.org/wiki/Data_Primitives"
 * target="n2">Data_Primitives</a>
 * 
 * <pre>
 * The structure is the following:
 * 
 *     an XML suffix introducing the UTF-8 character encoding for the file
 *     an osm element, containing the version of the API (and thus the features used) and the generator that distilled this file (e.g. an editor tool)
 *         a block of nodes containing especially the location in the WGS84 reference system
 *             the tags of each node
 *         a block of ways
 *             the references to its nodes for each way
 *             the tags of each way
 *         a block of relations
 *             the references to its members for each relation
 *             the tags of each relation
 * </pre>
 * 
 * @author lussopi
 */
public class OsmDAO extends BaseDAO {

	public OsmDAO(IDatabaseConnection connection) {

		super(connection);
	}

	public Long createOSM_Bounds(OSM_Bounds bounds) throws Exception {

		String insert = "INSERT INTO OSM_BOUNDS (ORIGINID,GEOMETRY) VALUES(?,ST_GeomFromText(?, 4326))";

		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, bounds.getOriginID());
		stmt.setString(2, GeoDB.gToWKT(bounds.getGeometry()));

		stmt.executeUpdate();

		stmt.close();

		return lastGeneratedId_BOUNDS();
	}

	private Long lastGeneratedId_BOUNDS() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM OSM_BOUNDS");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	// ---

	public void createOSM_Node(OSM_Node node) throws Exception {

		String insert = "INSERT INTO OSM_NODE (ORIGINID,OSMID,GEOMETRY) VALUES(?,?,ST_GeomFromText(?, 4326))";

		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, node.getOriginID());
		stmt.setLong(2, node.getOsmID());
		stmt.setString(3, GeoDB.gToWKT(node.getGeometry()));

		stmt.executeUpdate();

		stmt.close();
	}

	public OSM_Node findNodeByOsmID(Long osmID) throws Exception {

		String sql = "SELECT ID,OSMID,ORIGINID,ST_AsText(GEOMETRY) FROM OSM_NODE WHERE OSMID = " + String.valueOf(osmID);

		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery(sql);

		OSM_Node node = null;

		if (result.next()) {

			node = new OSM_Node(result.getLong(1), osmID, result.getLong(3), GeoUtils.getGeometryFromPolygon(result.getString(4)));
		}

		stmt.close();

		return node;
	}

	private Long lastGeneratedId_NODE() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM OSM_NODE");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	// ---

	public void createOSM_Tag(OSM_Tag tag) throws Exception {

		String insert = "INSERT INTO OSM_TAG (ORIGINID,TKEY,TVALUE,#TYPEID#) VALUES(?,?,?,?)";

		if (tag.getNodeID() != null) {
			insert = insert.replaceAll("#TYPEID#", "NODEID");
		} else if (tag.getWayID() != null) {
			insert = insert.replaceAll("#TYPEID#", "WAYID");
		} else if (tag.getRelationID() != null) {
			insert = insert.replaceAll("#TYPEID#", "RELATIONID");
		}

		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, tag.getOriginID());
		stmt.setString(2, tag.getKey());
		stmt.setString(3, tag.getValue());

		if (tag.getNodeID() != null) {
			stmt.setLong(4, tag.getNodeID());
		} else if (tag.getWayID() != null) {
			stmt.setLong(4, tag.getWayID());
		} else if (tag.getRelationID() != null) {
			stmt.setLong(4, tag.getRelationID());
		}

		stmt.executeUpdate();

		stmt.close();
	}

	private Long lastGeneratedId_TAG() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM OSM_TAG");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	// ---

	/**
	 * Enrich geometry in a second step
	 * 
	 * @param way
	 * @param spatialData
	 * @return
	 * @throws Exception
	 */
	public Long createOSM_Way(OSM_Way way, String spatialData) throws Exception {

		String insert = "INSERT INTO OSM_WAY (ORIGINID,OSMID,GEOMETRY) VALUES(?,?,ST_GeomFromText(?, 4326))";

		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, way.getOriginID());
		stmt.setLong(2, way.getOsmID());
		stmt.setString(3, spatialData);

		stmt.executeUpdate();

		stmt.close();

		return lastGeneratedId_WAY();
	}

	private Long lastGeneratedId_WAY() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM OSM_WAY");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}

	// ---
	public void createOSM_WayNodeRef(OSM_Way_Node_Ref wayNodeRef) throws Exception {

		String insert = "INSERT INTO OSM_WAY_NODE_REF (WAYID,NODEID) VALUES(?,?)";

		PreparedStatement stmt = getConnection().getConnection().prepareStatement(insert);

		stmt.setLong(1, wayNodeRef.getWayID());
		stmt.setLong(2, wayNodeRef.getNodeID());

		stmt.executeUpdate();

		stmt.close();
	}

	private Long lastGeneratedId_OSM_WAY_NODE_REF() throws SQLException, ClassNotFoundException {
		Statement stmt = getConnection().getConnection().createStatement();
		ResultSet result = stmt.executeQuery("SELECT MAX(ID) FROM OSM_WAY_NODE_REF");

		if (result.next()) {
			return result.getLong(1);
		}
		return null;
	}
}
